using System;
using System.Data.SQLite;
using System.Threading;
using dbm2.Model;
using dbm2.Model.Types;

namespace dbm2.Syncronizers
{
    public class SqliteSyncronizer :Syncronizer
    {
        private SQLiteConnection connection;
        private SQLiteDataReader reader;

        public SqliteSyncronizer(SQLiteConnection connection, DBModel model) : base(model)
        {
            this.connection = connection;
        }

        protected override void ConnectionOpen()
        {
            connection.Open();
        }

        protected override void ConnectionClose()
        {
            connection.Close();
        }
        protected override void LoadTables()
        {
            using(SQLiteCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM sqlite_master WHERE type='table'";
                using(reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                        model.AddTable(new Table(reader[1].ToString()));
                }
            }

        }

        protected override void LoadFields()
        {
            using (SQLiteCommand cmd = connection.CreateCommand())
            {
                for (int i = 0; i < model.CountTables(); i++)
                {
                    cmd.CommandText = "pragma table_info(" + model.GetTables()[i].Name + ")";
                    using (reader = cmd.ExecuteReader())
                    {
                        while (reader.HasRows && reader.Read())
                        {
                            Table table = model.GetTables()[i];
                            table.AddField(CreateField());
                        }
                    }
                }
            }
            LoadFKS();
        }

        private void LoadFKS()
        {
            foreach (var table in model.GetTables())
                LoadFKForTable(table);
        }

        protected override void LoadIndexes()
        {
            using(SQLiteCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM sqlite_master WHERE type = 'index'";
                using (reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                    {
                        Index index = new Index(reader["name"].ToString());
                        model.GetTable(reader["tbl_name"].ToString()).AddIndex(index);
                        index.Description = reader["sql"].ToString();
                    }
                }
            }
        }

        private Field CreateField()
        {
            Field field = new Field(reader[1].ToString());
            
            LoadConstraints(field);

            field.Type = CreateFieldType();
            
            return field;
        }

        private DBMType CreateFieldType()
        {
            string dbType = reader[2].ToString();
            DBMType type;
            int indexOf = dbType.IndexOf('(');
            if (indexOf > 0)
            {
                type = new DBMType(dbType.Substring(0, indexOf).ToLower());
                LoadTypeInfo(type, dbType);
            }
            else
                type =  new DBMType(dbType.ToLower());

            return type;
        }

        private void LoadTypeInfo(DBMType type, string dbType)
        {
            string parameters = dbType.Substring(dbType.IndexOf('(')+1, dbType.Length - dbType.IndexOf('(') - 2 );
            string[] parts = parameters.Split(new[] {','}, int.MaxValue);
            if(parts.Length == 2)
            {
                type.Precision = int.Parse(parts[0]);
                type.Scale = int.Parse(parts[1]);
            }
            if(parts.Length == 1)
            {
                type.Lenght = int.Parse(parts[0]);
            }
        }

        private void LoadConstraints(Field field)
        {
            if (reader["notnull"].ToString().Equals("1"))
                field.Constraint.IsNullable = false;
            if (!string.IsNullOrEmpty(reader["dflt_value"].ToString()))
                field.Constraint.SetDefaultValue(reader["dflt_value"].ToString());
            if (reader["pk"].ToString().Equals("1"))
                field.Constraint.IsPK = true;
        }

        private void LoadFKForTable(Table table)
        {
            using(SQLiteCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "PRAGMA foreign_key_list(" + table.Name + ")";
                using(reader = cmd.ExecuteReader())
                {
                    while(reader.HasRows && reader.Read())
                    {
                        Field pkField = model.GetTable(reader["table"].ToString()).GetField(reader["to"].ToString());
                        Field fkField = table.GetField(reader["from"].ToString());
                        fkField.Constraint.SetFKField(pkField, "FK_" + table.Name + fkField.Name);
                    }
                }
            }
        }

        protected override void LoadViews()
        {
            using (SQLiteCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM sqlite_master WHERE type = 'view' ";
                using (reader = cmd.ExecuteReader())
                {
                    while (reader.HasRows && reader.Read())
                    {
                        View view = new View(reader["name"].ToString());
                        view.SetDefinition(reader["sql"].ToString());
                        model.AddView(view);
                    }
                }
            }

        }
    }
}